const Service = require('egg').Service;
const lodash = require('lodash');

class SysRoleService extends Service {
    /**
     * 新增
     * @param {提交的数据} data 
     * @return 1，0
     */
    async create(data) {
        const { ctx, app } = this;
        let rs = await ctx.model.SysRole.create(data);
    }

    /**
     * 编辑
     * @param {提交的数据} data 
     * @return 1，0
     */
    async update(data, role_id) {
        const { ctx, app } = this;
        let rs = await ctx.model.SysRole.update(data, { where: { role_id } });
    }

    /**
     * 查询列表
     * @param {提交的数据} data 
     * @return 1，0
     */
    async querylist(queryParams, pageSize, page) {
        const { ctx, app } = this;
        queryParams = queryParams || {};
        //方便更改,使用 sql 查询,以下步骤为生产sql的过程
        let whereSQL = "where";
        let whereParams = {};
        if (! await app.utils.validator.isEmpty(queryParams['search'])) {
            whereParams['search'] = "%" + queryParams['search'] + "%";
            if (whereSQL == "where") {
                whereSQL = whereSQL + ' (t.role_name Like :search or t.role_key Like :search)';
            } else {
                whereSQL = whereSQL + ' and (t.role_name Like :search or t.role_key Like :search)';
            }
        }
        if (! await app.utils.validator.isEmpty(queryParams['status'])) {
            whereParams['status'] = queryParams['status'];
            if (whereSQL == "where") {
                whereSQL = whereSQL + ' t.status Like :status';
            } else {
                whereSQL = whereSQL + ' and t.status Like :status';
            }
        }
        if (whereSQL == "where") {
            whereSQL = "where t.deleted='0'"
        } else {
            whereSQL = whereSQL + " and t.deleted='0'"
        }
        //增加页码
        page = (page || 1) * 1;
        pageSize = (pageSize || 10) * 1;
        let dataWhereParams = { ...whereParams };
        dataWhereParams.offset = (page - 1) * pageSize;
        dataWhereParams.limit = pageSize;
        let dataSql = "SELECT * FROM sys_role t " + whereSQL + " LIMIT :offset, :limit";
        let countSql = "SELECT count(*) as total FROM sys_role t " + whereSQL;
        let datalist = await ctx.model.query(dataSql,
            { replacements: dataWhereParams, raw: true, type: app.Sequelize.QueryTypes.SELECT });
        //查询总数
        let count = await ctx.model.query(countSql,
            { replacements: whereParams, raw: true, type: app.Sequelize.QueryTypes.SELECT });
        return {
            datalist,
            totalSize: count[0].total
        }
    }

    /**
     * 删除
     * @param {提交的数据} data 
     * @return 1，0
     */
    async delete(id) {
        const { ctx, app } = this;
        let rs = await ctx.model.SysRole.destroy({ "where": { role_id: id } });
    }

    /**
     * 删除
     * @param {提交的数据} data 
     * @return 1，0
     */
    async deleteLogic(id) {
        const { ctx, app } = this;
        let rs = await ctx.model.SysRole.update({ deleted: "1" }, { "where": { role_id: id } });
    }

    /**
     * 查询详情
     * @param {提交的数据} data 
     * @return 1，0
     */
    async queryById(id) {
        const { ctx, app } = this;
        let rs = await ctx.model.SysRole.findOne({ "where": { role_id: id } });
        return rs;
    }

    /**
     * 根据角色id，查询列表
     * @param {*} role_id 
     */
    async queryMenuByRoleId(role_id) {
        const { ctx, app } = this;
        let querysql = `SELECT * FROM sys_role_menu t left join sys_menu m on m.menu_id=t.menu_id where t.role_id=:role_id and m.deleted='0'`;
        let datalist = await ctx.model.query(querysql,
            { replacements: { role_id }, raw: true, type: app.Sequelize.QueryTypes.SELECT });
        return datalist || [];
    }

    /**
     * 根据角色id，查询列表
     * @param {*} role_id 
     */
    async setRoleMenu(role_id, setMenus) {
        const { ctx, app } = this;
        let int = 0;
        let querysql = `DELETE FROM sys_role_menu where role_id=:role_id`;
        let del = await ctx.model.query(querysql,
            { replacements: { role_id }, raw: true, type: app.Sequelize.QueryTypes.DELETE });
        //开始插入新的数据
        let userid = await ctx.service.auth.getColonyUserId();
        let invalues = [];
        for (var i = 0; i < setMenus.length; i++) {
            invalues.push(`('${role_id}','${setMenus[i]}','${userid}',now())`);
        }
        let insertql = `INSERT INTO sys_role_menu (role_id, menu_id,create_by,create_time) VALUES${invalues.join(",")}`;
        if (setMenus.length > 0) {
            int = await ctx.model.query(insertql,
                { replacements: {}, raw: true, type: app.Sequelize.QueryTypes.INSERT });
        }
        return {
            DELETE: del,
            INSERT: int
        }
    }

    /**
 * 根据角色id，查询功能列表
 * @param {*} role_id 
 */
    async queryFunctionByRoleId(role_id) {
        const { ctx, app } = this;
        let querysql = `SELECT * FROM sys_role_function t left join sys_function m on m.function_id=t.function_id where t.role_id=:role_id and m.deleted='0'`;
        let datalist = await ctx.model.query(querysql,
            { replacements: { role_id }, raw: true, type: app.Sequelize.QueryTypes.SELECT });
        return datalist || [];
    }

    /**
     * 根据角色id，查询列表
     * @param {*} role_id 
     */
    async setRoleFunction(role_id, setFunctions) {
        const { ctx, app } = this;
        let int = 0;
        let querysql = `DELETE FROM sys_role_function where role_id=:role_id`;
        let del = await ctx.model.query(querysql,
            { replacements: { role_id }, raw: true, type: app.Sequelize.QueryTypes.DELETE });
        //开始插入新的数据
        let userid = await ctx.service.auth.getColonyUserId();
        let invalues = [];
        for (var i = 0; i < setFunctions.length; i++) {
            invalues.push(`('${role_id}','${setFunctions[i]}','${userid}',now())`);
        }
        let insertql = `INSERT INTO sys_role_function (role_id, function_id,create_by,create_time) VALUES${invalues.join(",")}`;
        if (setFunctions.length > 0) {
            int = await ctx.model.query(insertql,
                { replacements: {}, raw: true, type: app.Sequelize.QueryTypes.INSERT });
        }
        return {
            DELETE: del,
            INSERT: int
        }
    }

    /**
     * 根据userid，查询角色id
     * @param {*} role_id 
     */
    async queryRoleByUserId(user_id) {
        const { ctx, app } = this;
        let querysql = `SELECT * FROM sys_user_role t left join sys_role r on r.role_id=t.role_id where t.user_id=:user_id and r.deleted='0'`;
        let datalist = await ctx.model.query(querysql,
            { replacements: { user_id }, raw: true, type: app.Sequelize.QueryTypes.SELECT });
        return datalist || [];
    }

}

module.exports = SysRoleService;